use {
    crate::model::dapp_transaction::{DAppTransaction, DAppTransactionVO, TransactionStatus},
    sqlx::{MySql, Pool, Row},
};

impl DAppTransaction {
    pub fn from(
        chain: String,
        contract: String,
        from_addr: String,
        recharge_addr: String,
        recharge_hash: String,
        time_stamp: String,
        block_number: String,
        num: String,
        decimals: u8,
    ) -> Self {
        DAppTransaction {
            id: None,
            chain,
            contract,
            from_addr,
            recharge_addr,
            recharge_hash,
            collect_addr: None,
            collect_hash: None,
            time_stamp,
            block_number,
            num,
            decimals,
            status: 0,
        }
    }

    pub async fn insert(pool: &Pool<MySql>, one: &Self) -> anyhow::Result<u64> {
        let id = sqlx::query(r#"
        INSERT INTO dapp_transaction(chain, contract, from_addr, recharge_addr, recharge_hash, time_stamp, block_number, num, decimals, status)
            VALUES (?,?,?,?,?,?,?,?,?,?)"#)
            .bind(one.chain.clone())
            .bind(one.contract.clone())
            .bind(one.from_addr.clone())
            .bind(one.recharge_addr.clone())
            .bind(one.recharge_hash.clone())
            .bind(one.time_stamp.clone())
            .bind(one.block_number.clone())
            .bind(one.num.clone())
            .bind(one.decimals.clone())
            .bind(one.status.clone())
            .execute(pool)
            .await?
            .last_insert_id();
        Ok(id)
    }

    pub async fn insert_batch(pool: &Pool<MySql>, batch: &[Self]) -> anyhow::Result<bool> {
        let mut tx = pool.begin().await?;

        for one in batch {
            sqlx::query(r#"INSERT INTO dapp_transaction(chain, contract, from_addr, recharge_addr, recharge_hash, time_stamp, block_number, num, decimals, status) VALUES (?,?,?,?,?,?,?,?,?,?)"#)
                .bind(one.chain.clone())
                .bind(one.contract.clone())
                .bind(one.from_addr.clone())
                .bind(one.recharge_addr.clone())
                .bind(one.recharge_hash.clone())
                .bind(one.time_stamp.clone())
                .bind(one.block_number.clone())
                .bind(one.num.clone())
                .bind(one.decimals.clone())
                .bind(one.status.clone())
                .execute(&mut *tx)
                .await?
            ;
        }
        tx.commit().await?;

        Ok(true)
    }

    pub async fn exists(chain: &str, pool: &Pool<MySql>, hash: &str) -> anyhow::Result<bool> {
        let query = sqlx::query(
            r#"SELECT count(*) as count FROM dapp_transaction where chain =? and recharge_hash = ?"#,
        )
        .bind(chain)
        .bind(hash);

        let row = query.fetch_one(pool).await?;

        let count = row.get::<i64, _>("count");

        let ret = count > 0;
        Ok(ret)
    }

    pub async fn select_all_unprocessed(
        chain: &str,
        pool: &Pool<MySql>,
    ) -> anyhow::Result<Vec<DAppTransaction>> {
        let query = sqlx::query_as::<_, DAppTransaction>(
            r#"SELECT * FROM dapp_transaction where `chain` = ? and status = ?"#,
        )
        .bind(chain.to_string())
        .bind(u8::from(TransactionStatus::Unprocessed));
        let rows = query.fetch_all(pool).await?;
        Ok(rows)
    }

    pub async fn select_all_recharge_successed(
        chain: &str,
        pool: &Pool<MySql>,
    ) -> anyhow::Result<Vec<DAppTransactionVO>> {
        let query = sqlx::query_as::<_, DAppTransactionVO>(r#"
            select dt.*,
            dc.pr as contract_key,
            case
                when dt.`chain`= 'BSC' then dw.eth_pr
                when dt.`chain`= 'TRON' then dw.tron_pr
            end as from_key
            from dapp_transaction dt, dapp_wallet dw, dapp_contract dc
            where dt.contract = dc.addr and dt.`chain` = ? and dt.status = ?
            "#)
            .bind(chain.to_string())
            .bind(u8::from(TransactionStatus::RechargeSuccess));
        let rows = query.fetch_all(pool).await?;
        Ok(rows)
    }

    pub async fn update_status(pool: &Pool<MySql>, id: u64, status: u8) {
        let _execute_ret = sqlx::query(r#"UPDATE dapp_transaction SET status = ? WHERE id = ?"#)
            .bind(status)
            .bind(id)
            .execute(pool)
            .await;
    }

    pub async fn update_collect_info(
        pool: &Pool<MySql>,
        id: u64,
        status: u8,
        addr: &str,
        hash: &str,
    ) {
        let _execute_ret = sqlx::query(r#"UPDATE dapp_transaction SET status = ?, collect_addr = ?, collect_hash = ? WHERE id = ?"#)
            .bind(status)
            .bind(addr)
            .bind(hash)
            .bind(id)
            .execute(pool)
            .await;
    }
}
